<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Grafik_model extends CI_Model {

    function __construct() {
        parent::__construct();
        $this->CI = get_instance();
    }

   
    function jumlah_peserta_perlisensi() {
        $this->db->select('count(ID_PESERTA) as HASIL, NAMA_LISENSI');
        $this->db->from('PESERTA');
		$this->db->join('BATCH', 'PESERTA.ID_BATCH = BATCH.ID_BATCH', 'left');
		$this->db->join('LISENSI', 'BATCH.ID_LISENSI = LISENSI.ID_LISENSI', 'left');
        $this->db->where("PESERTA.JUMLAH > 0");
        $this->db->group_by('BATCH.ID_LISENSI');
		$this->db->order_by('LISENSI.NAMA_LISENSI', 'asc');
        $query = $this->db->get();
        return $query;
    }
	
	
	// function pertumbuhan_peserta_lama($id_lisensi) {
        // $this->db->select('count(ID_PESERTA) as HASIL, YEAR(TANGGAL_PEMBAYARAN) as TAHUN');
        // $this->db->from('PESERTA');
		// $this->db->join('BATCH', 'PESERTA.ID_BATCH= BATCH.ID_BATCH', 'left');
		// $this->db->join('LISENSI', 'BATCH.ID_LISENSI = LISENSI.ID_LISENSI', 'left');
        // $this->db->where("PESERTA.JUMLAH > 0");
		// if($this->session->userdata('id_hak') == 2){
			// $this->db->where("BATCH.ID_LISENSI = ".$this->session->userdata('id_lisensi')."");
		// }
		// if($id_lisensi != ''){
			// $this->db->where("BATCH.ID_LISENSI = ".$id_lisensi."");
		// }
        // $this->db->group_by('YEAR(TANGGAL_PEMBAYARAN)');
        // $this->db->order_by('YEAR(TANGGAL_PEMBAYARAN)', 'asc');
        // $query = $this->db->get();
        // return $query;
    // }
	
	function pertumbuhan_peserta($tahun, $id_lisensi) {
        $this->db->select('count(ID_PESERTA) as HASIL');
        $this->db->from('PESERTA');
		$this->db->join('BATCH', 'PESERTA.ID_BATCH= BATCH.ID_BATCH', 'left');
		$this->db->join('LISENSI', 'BATCH.ID_LISENSI = LISENSI.ID_LISENSI', 'left');
        $this->db->where("PESERTA.JUMLAH > 0");
        $this->db->where('YEAR(TANGGAL_PEMBAYARAN)', $tahun);
		if($this->session->userdata('id_hak') == 2){
			$this->db->where("BATCH.ID_LISENSI = ".$this->session->userdata('id_lisensi')."");
		}
		if($id_lisensi != ''){
			$this->db->where("BATCH.ID_LISENSI = ".$id_lisensi."");
		}
        $query = $this->db->get();
        return $query;
    }
	
	
	function peserta_per_pekerjaan($id_lisensi) {
        $this->db->select('count(ID_PESERTA) as HASIL, PEKERJAAN');
        $this->db->from('PESERTA');
		$this->db->join('BATCH', 'PESERTA.ID_BATCH= BATCH.ID_BATCH', 'left');
		$this->db->join('LISENSI', 'BATCH.ID_LISENSI = LISENSI.ID_LISENSI', 'left');
        $this->db->where("PESERTA.JUMLAH > 0");
		if($this->session->userdata('id_hak') == 2){
			$this->db->where("BATCH.ID_LISENSI = ".$this->session->userdata('id_lisensi')."");
		}
		if($id_lisensi != ''){
			$this->db->where("BATCH.ID_LISENSI = ".$id_lisensi."");
		}
        $this->db->group_by('PEKERJAAN');
        $this->db->order_by('PEKERJAAN', 'asc');
        $query = $this->db->get();
        return $query;
    }
	
	
	function peserta_per_jenkel($id_lisensi) {
        $this->db->select('count(ID_PESERTA) as HASIL, JENIS_KELAMIN');
        $this->db->from('PESERTA');
		$this->db->join('BATCH', 'PESERTA.ID_BATCH= BATCH.ID_BATCH', 'left');
		$this->db->join('LISENSI', 'BATCH.ID_LISENSI = LISENSI.ID_LISENSI', 'left');
        $this->db->where("PESERTA.JUMLAH > 0");
		if($this->session->userdata('id_hak') == 2){
			$this->db->where("BATCH.ID_LISENSI = ".$this->session->userdata('id_lisensi')."");
		}
		if($id_lisensi != ''){
			$this->db->where("BATCH.ID_LISENSI = ".$id_lisensi."");
		}
        $this->db->group_by('JENIS_KELAMIN');
        $this->db->order_by('JENIS_KELAMIN', 'asc');
        $query = $this->db->get();
        return $query;
    }

    

}

?>